package com.cennavi.minemap.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.springframework.boot.autoconfigure.EnableAutoConfiguration;
import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Component;

import com.cennavi.minemap.bean.SoluBean;
import com.cennavi.minemap.bean.ThumbnailBean;
import com.cennavi.minemap.bean.UploadSoluBean;

@Component
@EnableAutoConfiguration(exclude = { DataSourceAutoConfiguration.class })
public class SoluDao extends AbstractDao {

	public int insert(final SoluBean soluBean) {
		jdbcTemplate.setDataSource(pgDataSource);
		final String sql = "INSERT INTO MM_SOLUTION (name, describe, icon, createTime, "
				+ "lastUpdateTime, userId, template, center, bearing, "
				+ "pitch, status, zoom, minZoom, maxZoom, isTemplate, config) "
				+ "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
		KeyHolder keyHolder = new GeneratedKeyHolder();
		jdbcTemplate.update(new PreparedStatementCreator() {
			public PreparedStatement createPreparedStatement(Connection conn) throws SQLException {
				PreparedStatement ps = conn.prepareStatement(sql,
						new String[] { "id" });
				ps.setString(1, soluBean.getName());
				ps.setString(2, soluBean.getDescribe());
				ps.setString(3, soluBean.getIcon());
				ps.setString(4, soluBean.getCreateTime());
				ps.setString(5, soluBean.getLastUpdateTime());
				ps.setInt(6, soluBean.getUserId());
				ps.setString(7, soluBean.getTemplate());
				ps.setString(8, soluBean.getCenter());
				ps.setDouble(9, soluBean.getBearing());
				ps.setDouble(10, soluBean.getPitch());
				ps.setInt(11, soluBean.getStatus());
				ps.setDouble(12, soluBean.getZoom());
				ps.setDouble(13, soluBean.getMinZoom());
				ps.setDouble(14, soluBean.getMaxZoom());
				ps.setInt(15, soluBean.getIsTemplate());
				ps.setString(16, soluBean.getConfig()==null?"":soluBean.getConfig());
				return ps;
			}
		}, keyHolder);
		return keyHolder.getKey().intValue();
	}

	public int update(final SoluBean soluBean) {
		jdbcTemplate.setDataSource(pgDataSource);
		try {
			String setSql = super.getSetSql(soluBean);
			String sql = "UPDATE MM_SOLUTION SET " + setSql + " WHERE id = ?";
			jdbcTemplate.update(sql, new PreparedStatementSetter() {
				@Override
				public void setValues(PreparedStatement ps) throws SQLException {
					ps.setInt(1, soluBean.getId());
				}
			});
			return 0;
		} catch (Exception e) {
			e.printStackTrace();
			return -1;
		}
	}

	public int delete(int id) {
		jdbcTemplate.setDataSource(pgDataSource);
		try {
			String sql = "DELETE FROM MM_SOLUTION WHERE id = ?";
			jdbcTemplate.update(sql, new Object[] { id },
					new int[] { java.sql.Types.INTEGER });
			return 0;
		} catch (Exception e) {
			e.printStackTrace();
			return -1;
		}
	}

	public List<SoluBean> queryIdNameByUserId(int userId) {
		jdbcTemplate.setDataSource(pgDataSource);
		String sql = "SELECT id, name FROM MM_SOLUTION WHERE userId = ?";
		return (List<SoluBean>) jdbcTemplate.query(sql, new Object[] { userId }, new SoluIdNameMapper());
	}
	
	public List<SoluBean> queryByUserId(int userId) {
		jdbcTemplate.setDataSource(pgDataSource);
		String sql = "SELECT id, name, describe, icon, createTime, lastUpdateTime, publishTime, "
				+ "userId, template, center, bearing, pitch, status, zoom, minZoom, maxZoom, "
				+ "isTemplate, url, config FROM MM_SOLUTION WHERE userId = ? ORDER BY ID DESC";
		return (List<SoluBean>) jdbcTemplate.query(sql, new Object[] { userId }, new SoluMapper());
	}
	
	public List<SoluBean> queryTemplate() {
		jdbcTemplate.setDataSource(pgDataSource);
		String sql = "SELECT id, name, describe, icon, createTime, lastUpdateTime, publishTime, "
				+ "userId, template, center, bearing, pitch, status, zoom, minZoom, maxZoom, "
				+ "isTemplate, url, config FROM MM_SOLUTION WHERE isTemplate = 1 ORDER BY ID";
		return (List<SoluBean>) jdbcTemplate.query(sql, new SoluMapper());
	}
	
	public List<SoluBean> queryTemplate2() {
		jdbcTemplate.setDataSource(pgDataSource);
		String sql = "SELECT id, name, describe, icon, createTime, lastUpdateTime, publishTime, "
				+ "userId, template, center, bearing, pitch, status, zoom, minZoom, maxZoom, "
				+ "isTemplate, url, config FROM MM_SOLUTION WHERE isTemplate = 2 AND status = 1 ORDER BY ID";
		return (List<SoluBean>) jdbcTemplate.query(sql, new SoluMapper());
	}
	
	public SoluBean queryById(int id) {
		jdbcTemplate.setDataSource(pgDataSource);
		String sql = "SELECT id, name, describe, icon, createTime, lastUpdateTime, publishTime, "
				+ "userId, template, center, bearing, pitch, status, zoom, minZoom, maxZoom, "
				+ "isTemplate, url, config FROM MM_SOLUTION WHERE id = ?";
		return jdbcTemplate.queryForObject(sql, new Object[] { id }, new SoluMapper());
	}
	
	public SoluBean queryByUrl(String url) {
		jdbcTemplate.setDataSource(pgDataSource);
		String sql = "SELECT id, name, describe, icon, createTime, lastUpdateTime, publishTime, "
				+ "userId, template, center, bearing, pitch, status, zoom, minZoom, maxZoom, "
				+ "isTemplate, url, config FROM MM_SOLUTION WHERE url = ?";
		return jdbcTemplate.queryForObject(sql, new Object[] { url }, new SoluMapper());
	}
	
	public int publish(final SoluBean soluBean) {
		jdbcTemplate.setDataSource(pgDataSource);
		try {
			String sql = "UPDATE MM_SOLUTION SET lastUpdateTime = ?, publishTime = ?, status = 1, url = ? WHERE id = ?";
			jdbcTemplate.update(sql, new PreparedStatementSetter() {
				@Override
				public void setValues(PreparedStatement ps) throws SQLException {
					ps.setString(1, soluBean.getLastUpdateTime());
					ps.setString(2, soluBean.getPublishTime());
					ps.setString(3, soluBean.getUrl());
					ps.setInt(4, soluBean.getId());
				}
			});
			return 0;
		} catch (Exception e) {
			e.printStackTrace();
			return -1;
		}
	}
	
	protected class SoluMapper implements RowMapper<SoluBean> {   
        public SoluBean mapRow(ResultSet rs, int rowNum) throws SQLException {   
			SoluBean solu = new SoluBean();
			solu.setId(rs.getInt("id"));
			solu.setName(rs.getString("name"));
			solu.setDescribe(rs.getString("describe"));
			solu.setIcon(rs.getString("icon"));
			solu.setCreateTime(rs.getString("createTime"));
			solu.setLastUpdateTime(rs.getString("lastUpdateTime"));
			solu.setPublishTime(rs.getString("publishTime"));
			solu.setUserId(rs.getInt("userId"));
			solu.setTemplate(rs.getString("template"));
			solu.setCenter(rs.getString("center"));
			solu.setBearing(rs.getDouble("bearing"));
			solu.setPitch(rs.getDouble("pitch"));
			solu.setStatus(rs.getInt("status"));
			solu.setZoom(rs.getDouble("zoom"));
			solu.setMinZoom(rs.getDouble("minZoom"));
			solu.setMaxZoom(rs.getDouble("maxZoom"));
			solu.setIsTemplate(rs.getInt("isTemplate"));
			solu.setUrl(rs.getString("url"));
			solu.setConfig(rs.getString("config"));
			return solu;
        }   
    }
	
	
	protected class SoluIdNameMapper implements RowMapper<SoluBean> {   
        public SoluBean mapRow(ResultSet rs, int rowNum) throws SQLException {   
			SoluBean solu = new SoluBean();
			solu.setId(rs.getInt("id"));
			solu.setName(rs.getString("name"));
			return solu;
        }   
    }
	
	public List<SoluBean> querySoluListByUploadId(int uploadId) {
		jdbcTemplate.setDataSource(pgDataSource);
		String sql = "SELECT id, name, describe, icon, createTime, "
				+ "lastUpdateTime, publishTime, userId, template, "
				+ "center, bearing, pitch, status, zoom, minZoom,"
				+ "maxZoom, isTemplate, url, config FROM MM_SOLUTION "
				+ "WHERE id IN (SELECT soluid FROM MM_UPLOAD_SOLU WHERE uploadid = ?)";
		return (List<SoluBean>) jdbcTemplate.query(sql, new Object[] { uploadId }, new SoluMapper());
	}
	
	public int insertUploadSolu(UploadSoluBean uploadSoluBean){
		try {
			jdbcTemplate.setDataSource(pgDataSource);
			jdbcTemplate.execute("begin");

			Integer uploadId = uploadSoluBean.getUploadId();

			List<Integer> soluIds = uploadSoluBean.getSoluIds();
			for (int soluId : soluIds) {
				jdbcTemplate.update("INSERT INTO MM_UPLOAD_SOLU (uploadId, soluId) VALUES(?, ?)",
						new Object[] { uploadId, soluId });
			}
			jdbcTemplate.execute("commit");
			return 0;
		} catch (Exception e) {
			jdbcTemplate.execute("rollback");
			e.printStackTrace();
			return -1;
		}
	}
	
	public int deleteUploadSolu(UploadSoluBean tokenSolusBean){
		jdbcTemplate.setDataSource(pgDataSource);
		return jdbcTemplate.update("DELETE FROM MM_UPLOAD_SOLU WHERE uploadId = ?", new Object[] { tokenSolusBean.getUploadId() },
				new int[] { java.sql.Types.INTEGER });
	}
	
	public int insertThumbnail(final ThumbnailBean thumbnailBean) {
		jdbcTemplate.setDataSource(pgDataSource);
		final String sql = "INSERT INTO mm_thumbnail (soluId, thumbnail) VALUES (?, ?)";
		try{
			jdbcTemplate.update(sql, new PreparedStatementSetter() {
				@Override
				public void setValues(PreparedStatement ps) throws SQLException {
					ps.setInt(1, thumbnailBean.getSoluId());
					ps.setString(2, thumbnailBean.getThumbnail());
				}
			});
			return 0;
		} catch (Exception e) {
			e.printStackTrace();
			return -1;
		}

	}
	
	public int updateThumbnail(final ThumbnailBean thumbnailBean) {
		jdbcTemplate.setDataSource(pgDataSource);
		try {
			String sql = "UPDATE mm_thumbnail SET thumbnail = ? WHERE soluId = ?";
			jdbcTemplate.update(sql, new PreparedStatementSetter() {
				@Override
				public void setValues(PreparedStatement ps) throws SQLException {
					ps.setString(1, thumbnailBean.getThumbnail());
					ps.setInt(2, thumbnailBean.getSoluId());
				}
			});
			return 0;
		} catch (Exception e) {
			e.printStackTrace();
			return -1;
		}
	}
	
	public int deleteThumbnail(int soluId) {
		jdbcTemplate.setDataSource(pgDataSource);
		try {
			String sql = "DELETE FROM mm_thumbnail WHERE soluId = ?";
			jdbcTemplate.update(sql, new Object[] { soluId },
					new int[] { java.sql.Types.INTEGER });
			return 0;
		} catch (Exception e) {
			e.printStackTrace();
			return -1;
		}
	}

	public List<ThumbnailBean> queryThumbnail(int soluId) {
		jdbcTemplate.setDataSource(pgDataSource);
		String sql = "SELECT soluid, thumbnail FROM mm_thumbnail WHERE soluId = ?";
		return (List<ThumbnailBean>) jdbcTemplate.query(sql, new Object[] { soluId }, new ThumbnailMapper());
	}
	
	
	protected class ThumbnailMapper implements RowMapper<ThumbnailBean> {   
        public ThumbnailBean mapRow(ResultSet rs, int rowNum) throws SQLException {   
        	ThumbnailBean thumbnail = new ThumbnailBean();
        	thumbnail.setSoluId(rs.getInt("soluId"));
        	thumbnail.setThumbnail(rs.getString("thumbnail"));
			return thumbnail;
        }   
    }
}